package com.xiaowei.gym.gymjsp.ware.util;

import com.xiaowei.gym.gymjsp.ware.dao.BaseDao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {

    private static String DRIVER;
    private static String URL;
    private static String USERNAME;
    private static String PASSWORD;

    // 静态代码块，类加载的时候初始化
    static {
        // 通过类加载器读取对应的资源
        Properties properties = new Properties();
        // 通过类加载器读取对应的资源
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        DRIVER = properties.getProperty("driver");
        URL = properties.getProperty("url");
        USERNAME = properties.getProperty("username");
        PASSWORD = properties.getProperty("password");
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection=DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭数据库资源
     * @param conn
     * @param pstmt
     * @param rs
     */
    public static void closeResource(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行更新操作（增删改）
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            return pstmt.executeUpdate();
        } finally {
            closeResource(conn, pstmt, null);
        }
    }

    /**
     * 执行查询操作，并使用结果集处理器处理结果
     * @param sql
     * @param handler
     * @param params
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> T executeQuery(String sql, ResultSetHandler<T> handler, Object... params) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            if (params != null) {
                System.out.println("jdbcUtil的params:"+params.toString());
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            rs = pstmt.executeQuery();
            System.out.println("rs:"+rs.toString());
            return handler.handle(rs);
        } finally {
            closeResource(conn, pstmt, rs);
        }
    }

    // 批量处理方法
    public static int[] batchDelete(String sql, Object... params) throws SQLException {
        PreparedStatement ps = null;
        Connection conn = null;
        int[] ints = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);  // 关闭自动提交
            ps = conn.prepareStatement(sql);
            if (params != null){
                // 设置参数
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(1, params[i]);
                    ps.addBatch();
//                    ps.setObject(i + 1, params[i]);
                }
                ps.addBatch();  // 添加到批处理
            }
            ints = ps.executeBatch();// 执行批处理
            conn.commit();  // 提交事务
            if (ints != null){
                return ints;
            }
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback();  // 发生异常时回滚事务
            }
            throw e;
        } finally {
            // 关闭 PreparedStatement
//            if (ps != null) {
//                ps.close();
//            }
            // 恢复自动提交
            conn.setAutoCommit(true);
            closeResource(conn,ps,null);
        }
        return ints;
    }

    public void batchInsert(String sql, String[] names) {
        Connection connection = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (String name : names) {
                preparedStatement.setString(1, name);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            connection.commit();
        } catch (SQLException e) {
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void batchUpdate(String sql, String[] names) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < names.length; i++) {
                preparedStatement.setString(1, names[i]);
                preparedStatement.setInt(2, i + 1); // 假设id从1开始
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            connection.commit();
        } catch (SQLException e) {
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResource(connection,preparedStatement,null);
        }
    }

    /**
     * 开启事务
     * @param conn
     * @throws SQLException
     */
    public static void beginTransaction(Connection conn) throws SQLException {
        conn.setAutoCommit(false);
    }

    /**
     * 提交事务
     * @param conn
     * @throws SQLException
     */
    public static void commitTransaction(Connection conn) throws SQLException {
        conn.commit();
        conn.setAutoCommit(true);
    }

    /**
     * 回滚事务
     * @param conn
     * @throws SQLException
     */
    public static void rollbackTransaction(Connection conn) throws SQLException {
        conn.rollback();
        conn.setAutoCommit(true);
    }

    public interface ResultSetHandler<T> {
        T handle(ResultSet rs) throws SQLException;
    }
}
